from sqlalchemy import create_engine, Column, Integer, String, DateTime, Boolean, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import os
import logging
from dotenv import load_dotenv

# 配置日志
logger = logging.getLogger(__name__)

# 加载环境变量
load_dotenv()

# 数据库连接配置
DB_HOST = os.getenv("DB_HOST", "localhost")
DB_PORT = os.getenv("DB_PORT", "3306")
DB_USER = os.getenv("DB_USER", "root")
DB_PASSWORD = os.getenv("DB_PASSWORD", "")
DB_NAME = os.getenv("DB_NAME", "fastapi_app")

# 创建数据库连接URL
DATABASE_URL = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

# 记录数据库连接信息（不包括密码）
logger.info(f"数据库连接: {DB_USER}@{DB_HOST}:{DB_PORT}/{DB_NAME}")

try:
    # 创建数据库引擎
    engine = create_engine(
        DATABASE_URL,
        echo=False,  # 设置为True可以查看SQL语句
        pool_pre_ping=True,  # 连接池预检查
        pool_recycle=3600,  # 连接回收时间
    )
    
    # 测试连接
    with engine.connect() as conn:
        logger.info("数据库连接成功")
        
    # 创建会话工厂
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    
    # 创建基础模型类
    Base = declarative_base()
    
except Exception as e:
    logger.error(f"数据库连接失败: {e}", exc_info=True)
    # 在开发环境中，可以使用SQLite作为备用
    logger.warning("使用SQLite作为备用数据库")
    DATABASE_URL = "sqlite:///./fastapi_app.db"
    engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    Base = declarative_base()

# 获取数据库会话
def get_db():
    """
    获取数据库会话（生成器）
    
    用法:
    ```
    @app.get("/users/")
    def get_users(db: Session = Depends(get_db)):
        users = db.query(User).all()
        return users
    ```
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# 获取普通数据库会话（非生成器）
def get_db_session():
    """
    获取普通数据库会话（非生成器），适用于脚本和非FastAPI上下文
    
    用法:
    ```
    session = get_db_session()
    try:
        users = session.query(User).all()
        # 进行其他操作...
        session.commit()
    except Exception as e:
        session.rollback()
        raise
    finally:
        session.close()
    ```
    """
    return SessionLocal()

# 基础模型混入类
class TimestampMixin:
    """
    时间戳混入类，提供创建时间和更新时间字段
    """
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) 